using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using CoreLibrary;
using CoreLibrary.Sql;
using Entities.DataBase.Persistent;

namespace Entities.DataBase.Sql {

    internal class SelectPath : ISqlQuery<List<ProductCatalog>> {
        
        private readonly int _catid;
        private readonly string _tableName;

        public SelectPath(int catid,string tableName) {
            _catid = catid;
            _tableName = tableName;
        }

        public string GetSQL() {
            return string.Format(
            @"
                SELECT parent.*
                FROM {0} AS node,
                     {0} AS parent
                WHERE node.LID BETWEEN parent.LID AND parent.RID 
                AND node.ID = @id
                ORDER by parent.LID;", _tableName);
        }

        public void SetParameters(SqlCommand command) {
            command.Parameters.AddWithValue("@id", _catid);
        }

        public List<ProductCatalog> GetResult(SqlConnection conn, SqlDataReader result) {
            var cats = new List<ProductCatalog>();
            if (result.HasRows) {
                while(result.Read()) {
                   cats.Add(ReadCatalog(result));
                }
            }
            return cats;
        }

        private static ProductCatalog ReadCatalog(SqlDataReader result) {
            return new ProductCatalog {
                Id           = (int)result["ID"],
                LID          = (int)result["LID"],
                RID          = (int)result["RID"],
                PID          = (int)result["PID"],
                Level        = (int)result["LEVEL"],
                Name         = Util.GetString(result["NAME"]),
                Thumbs       = Util.GetString(result["THUMBS"]),
                Description  = Util.GetString(result["DESCRIPTION"]),
                Keywords     = Util.GetString(result["KEYWORDS"]),
                Enabled      = (bool)result["ENABLED"],
                ListOrder    = (int)result["LIST_ORDER"],
                DateCreated  = (DateTime)result["DATE_CREATED"],
                DateUpdated  = (DateTime)result["DATE_UPDATED"]
            };
        }
    }

}